import sqlite3

# 描述数据库表结构
db_schema = """
-- 订单详情表
CREATE TABLE orders (
    id INT PRIMARY KEY NOT NULL, -- 主键，不允许为空
    customerName STR NOT NULL, -- 客户名，不允许为空
    productName STR NOT NULL, -- 产品名称，不允许为空
    price DECIMAL(10,2) NOT NULL, -- 价格，不允许为空
    status INT NOT NULL, -- 订单状态，整数类型，不允许为空。1代表已支付，2代表已退款
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 创建时间，默认为当前时间
    refund_time TIMESTAMP -- 退款时间，可以为空
);
"""

cursor = None

# 初始化数据库
def initDB():
    global cursor
    # 创建数据库连接
    conn = sqlite3.connect(':memory:')
    cursor = conn.cursor()

    # 创建orders表
    cursor.execute(db_schema)

    # 插入模拟记录
    mock_data = [
        (1, '张三', '衬衫', 50, 1, '2024-02-15 13:24:00', None),
        (2, '王五', '外套', 200, 1, '2024-01-20 09:15:00', None),
        (3, '李四', '短袖', 80, 1, '2024-05-10 15:30:00', None),
        (4, '赵六', '裤子', 120, 1, '2024-04-05 11:45:00', None),
        (5, '张三', '外套', 200, 1, '2024-03-12 10:00:00', None),
        (6, '李四', '短袖', 80, 1, '2024-06-01 14:30:00', None),
        (7, '王五', '衬衫', 50, 2, '2024-02-28 16:15:00', '2024-03-02 10:30:00'),
        (8, '赵六', '裤子', 120, 1, '2024-01-10 08:45:00', None),
        (9, '张三', '短裤', 90, 1, '2024-04-20 12:00:00', None),
        (10, '李四', '外套', 200, 1, '2024-05-25 17:15:00', None),
        (11, '王五', '短袖', 80, 1, '2024-02-10 11:30:00', None),
        (12, '赵六', '衬衫', 50, 1, '2024-06-15 09:45:00', None),
        (13, '张三', '裤子', 120, 1, '2024-01-25 14:00:00', None),
        (14, '李四', '短裤', 90, 2, '2024-04-10 16:15:00', '2024-04-12 10:30:00'),
        (15, '王五', '外套', 200, 1, '2024-03-20 11:45:00', None),
        (16, '赵六', '短袖', 80, 1, '2024-05-05 13:00:00', None),
        (17, '张三', '衬衫', 50, 1, '2024-06-10 09:15:00', None),
        (18, '李四', '裤子', 120, 1, '2024-02-25 15:30:00', None),
        (19, '王五', '短袖', 80, 1, '2024-01-15 12:45:00', None),
        (20, '赵六', '短裤', 90, 1, '2024-03-10 10:00:00', None)
    ]

    for record in mock_data:
        cursor.execute(f'''
            INSERT INTO orders (
                       id, customerName, productName, price, status, 
                       create_time, refund_time)
            VALUES (?, ?, ?, ?, ?, ?, ?)
            ''', record)

    # 提交事务
    conn.commit()

# 执行SQL查询数据
def ask_database(query):
    global cursor
    cursor.execute(query)
    records = cursor.fetchall()
    return records